PostgreSQL dblink

1 背景知识

PostgreSQL 是一个功能强大的开源关系型数据库管理系统，它提供了多种扩展功能，以支持复杂的数据库操作和数据集成。其中，dblink 是 PostgreSQL 的一个扩展模块，允许用户从一个 PostgreSQL 数据库连接到另一个 PostgreSQL 数据库，执行远程查询并获取结果。本文将介绍 dblink 的基本概念、安装配置、使用方法以及一些实际应用场景。

dblink ，即 database link，是一个在 PostgreSQL 中实现数据库链接的扩展。通过 dblink ，用户可以在一个数据库会话中执行对另一个数据库的查询，就像查询本地表一样。这使得跨数据库的数据访问和管理变得更加方便。

2 安装与配置

2.1 安装 dblink 扩展

在 PostgreSQL 中安装 dblink 扩展的步骤如下：

1. 确认 PostgreSQL 数据库已经安装。
2. 连接到 testdb 数据库。
3. 在数据库中安装 dblink 扩展。可以使用以下 SQL 命令：

#postgre>
psql -U postgres -d testdb

su - postgres
#postgres>
psql -U postgres -d testdb -c "CREATE EXTENSION dblink CASCADE;"
psql -U postgres -d postgres -c "CREATE EXTENSION dblink CASCADE;"
psql -U postgres -d template1 -c "CREATE EXTENSION dblink CASCADE;"

2.2 创建dblink连接

使用 dblink 之前，需要在数据库中配置连接信息。这通常通过 dblink_get_connections 函数创建一个新的dblink链接来实现，例如，下面链接到 192.168.10.159 上的 PostgreSQL15 。

#testdb>
SELECT dblink_connect('remote_10.159_pg15', 'dbname=testdb user=postgres password=postgres host=192.168.10.159');

dblink_connect 
----------------
 OK
(1 row)

2.2.1 参数说明

2.2.2 配置链接的注意事项

完整的函数使用说明，请参考 dblink_connect 详细说明

ERROR:  could not establish connection
DETAIL:  connection to server at "192.168.10.159", port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

2.2.3 查看dblink 链接

使用 dblink_get_connections 函数，查看当前数据库系统中已创建的数据库链接

#testdb>
SELECT dblink_get_connections();

#testdb>
dblink_get_connections 
------------------------
 {remote_10.159_pg15}
(1 row)

3 dblink 实战

3.1 基本查询

使用 dblink 执行远程查询非常简单。以下是一个查询远程数据库中表的例子：

\x
SELECT * FROM dblink(
  'remote_10.159_pg15',
  'SELECT customer_id,
  store_id,
  first_name || last_name AS name,
  email 
  FROM public.customer
  WHERE customer_id=81' 
  ) 
AS t(customer_id integer,
	 store_id integer,
	 name character varying(255),
	 email character varying(255)
	 );

-[ RECORD 1 ]------------------------------------
customer_id | 81
store_id    | 1
name        | ANDREAHENDERSON
email       | ANDREA.HENDERSON@sakilacustomer.org

这里，dblink 函数的第一个参数是连接字符串，第二个参数是要执行的 SQL 查询。通过 t(customer_id integer,store_id integer,name character varying(255),email character varying(255)); 可以指定返回结果的列名和数据类型。

3.2 执行更新

dblink 不仅可以查询远程数据库，还可以执行更新、插入和删除操作。

3.2.1 更新一行数据

将 192.168.10.159 上的 PostgreSQL15 数据库中的 public.customer 的用户 ANDREAHENDERSON 所注册的商店 1 更改为 2。

SELECT dblink_exec('remote_10.159_pg15',
				   'UPDATE public.customer
				   SET store_id = 2
				   WHERE customer_id=81');

dblink_exec 
-------------
 UPDATE 1
(1 row)

3.2.2 查看数据是否更新

查询 192.168.10.159 上的 PostgreSQL15 数据库中的 public.customer 的用户 ANDREAHENDERSON 所注册的商店是否更改为 2。

\x
SELECT * FROM dblink(
  'remote_10.159_pg15',
  'SELECT customer_id,
  store_id,
  first_name || last_name AS name,
  email 
  FROM public.customer
  WHERE customer_id=81' 
  ) 
AS t(customer_id integer,
	 store_id integer,
	 name character varying(255),
	 email character varying(255)
	 );

-[ RECORD 1 ]------------------------------------
customer_id | 81
store_id    | 2
name        | ANDREAHENDERSON
email       | ANDREA.HENDERSON@sakilacustomer.org

3.3 关闭 dblink 链接

1. 断开连接名称为 remote_10.159_pg15 的数据库链接。

SELECT dblink_disconnect('remote_10.159_pg15');

dblink_disconnect 
-------------------
 OK
(1 row)

2. 查看 remote_10.159_pg15 数据库链接是否断开。

#testdb>
SELECT dblink_get_connections();
 dblink_get_connections 
------------------------
 
(1 row)

3.4 事务管理

dblink 支持在远程数据库上执行事务。使用 dblink_connect 函数 建立连接后，所有的操作都会在一个事务中执行，直到显式地提交或回滚。

4 适用场景

4.1 数据同步

dblink 可以用于实现两个数据库之间的数据同步。通过定期执行查询和更新操作，可以保持两个数据库的数据一致性。

4.2 报告和分析

在进行复杂的报告和数据分析时，可能需要从多个数据库中提取数据。使用 dblink ，可以在一个查询中联合多个数据库的数据，简化数据处理流程。

5 命令支持情况

5.1 支持SHOW命令

SELECT * FROM dblink(
  'remote_10.159_pg15',
  'SHOW shared_buffers' 
  ) 
AS t(shared_buffers character varying(255)
         );

shared_buffers 
----------------
 128MB
(1 row)

5.2 支持ALTER SYSTEM 命令

testdb=# SELECT dblink_exec('remote_10.159_pg15',  'ALTER SYSTEM SET work_mem=8192');
 dblink_exec  
--------------
 ALTER SYSTEM
(1 row)

5.3 不支持快捷命令

dblink 不支持 PostgreSQL 的快捷命令。

testdb=# SELECT * FROM dblink(
  'remote_10.159_pg15',
  '\dt' 
  ) 
AS t(Schema character varying(255),
         Name character varying(255),
         Type character varying(255),
         Owner character varying(255)
         );
ERROR:  syntax error at or near "\"
CONTEXT:  while executing query on dblink connection named "remote_10.159_pg15"

6 小结 & FAQ

6.1 dblink 使用的注意事项

使用 dblink 需要注意，要考虑到性能和安全性的问题，合理地设计和优化数据库链接。

6.2 dblink 函数

PostgreSQL dblink_connect 函数
PostgreSQL dblink dblink 函数
dblink_connect — 打开与远程数据库的持久连接
dblink_connect_u — 不安全地打开与远程数据库的持久连接
dblink_disconnect — 关闭与远程数据库的持久连接
dblink — 在远程数据库中执行查询
dblink_exec — 在远程数据库中执行命令
dblink_open — 在远程数据库中打开游标
dblink_fetch — 返回远程数据库中打开的游标中的行
dblink_close — 关闭远程数据库中的游标
dblink_get_connections — 返回所有打开的命名 dblink 连接的名称
dblink_error_message — 获取命名连接上的最后一条错误消息
dblink_send_query — 向远程数据库发送异步查询
dblink_is_busy — 检查异步查询的连接是否繁忙
dblink_get_notify — 检索连接上的异步通知
dblink_get_result — 获取异步查询结果
dblink_cancel_query — 取消对命名连接的任何活动查询
dblink_get_pkey — 返回关系主键字段的位置和字段名称
dblink_build_sql_insert — 使用本地元组构建 INSERT 语句，将主键字段值替换为提供的替代值
dblink_build_sql_delete — 使用为主键字段值提供的值构建 DELETE 语句
dblink_build_sql_update — 使用本地元组构建 UPDATE 语句，将主键字段值替换为提供的替代值

6.3 dblink 和外部表结合使用

dblink 还可以和PostgreSQL 外部表 结合起来使用，具体内容参考 PostgreSQL dblink 外部表。

6.4 参考链接

PostgreSQL: Documentation: 16: F.12. dblink — connect to other PostgreSQL databases